package com.example.mapp.buildSql;

import com.example.ext.entity.ExtParty;
import org.apache.ibatis.jdbc.SQL;

import java.util.Map;

/**
 * Created by youkun on 2017/9/9.
 */
public class PartyMappSql {

    public String queryList(ExtParty extParty){
        String select = " distinct p.id,p.party_name,p.party_addr,p.party_type,p.parent_id,pp.party_name as parentName,ele.ele_count,pro.pro_count,user.user_count ";
        String table =" party p left join (select id,party_name from party) pp on pp.id = p.parent_id left join sub_party_info spi on p.id=spi.id "
        		+ "left join (select ppp.id,CONCAT(count(1)) ele_count from party ppp inner join elevator e on ppp.id = e.used_party_id or ppp.id = e.maintenance_party_id group by ppp.id) ele on ele.id=p.id "
        		+ "left join (select ppp.id,CONCAT(count(SUBSTRING_INDEX(SUBSTRING_INDEX(pc.contract_Name,',',help_topic_id+1),',',-1))) pro_count from party ppp inner join party_contract pc on ppp.id=pc.first_party or ppp.id=pc.second_party " 
				+ "INNER JOIN mysql.help_topic on 1=1 "
				+ "WHERE help_topic_id < LENGTH(pc.contract_Name)-LENGTH(REPLACE(pc.contract_Name,',',''))+1 "
				+ "group by ppp.id) pro on pro.id=p.id "
        		+ "left join (select ppp.id,CONCAT(count(1)) user_count from party ppp inner join party_user pu on ppp.id=pu.party_id inner join sys_user_role sur on pu.user_id=sur.user_id where sur.role_id in ('5','6','7') group by ppp.id) user on user.id = p.id ";
        String whereParam="";
        if(extParty.getPartyName()!=null && extParty.getPartyName().length()>0){
            extParty.setPartyName("%"+extParty.getPartyName()+"%");
            whereParam+=" and p.party_name like #{partyName}";
        }
        if(extParty.getPartyType()!=null && extParty.getPartyType().length()>0){
            if(extParty.getPartyType().contains(",")){
                StringBuilder builder = new StringBuilder();
                String[] types = extParty.getPartyType().split(",");
                builder.append("and ( p.party_type = ").append(types[0]);
                for(int i = 1,len = types.length; i < len; i++){
                    builder.append(" or p.party_type =  ").append(types[i]);
                }
                whereParam+=builder.append(" ) ").toString();
            }else{
                whereParam+=" and p.party_type = #{partyType} ";
            }
        }
       if(extParty.getId() != null){
           whereParam += " and spi.parent_id = #{id} ";
       }
        String where = " 1=1 "+whereParam;
        return new SQL(){{
            SELECT(select);
            FROM(table);
            WHERE(where);
        }
        }.toString();
    }

    public String queryRandomList(Map map) {
        StringBuffer sb = new StringBuffer();

        sb.append(" select distinct adp.id,adp.party_name as partyName,adp.party_addr as partyAddr,adp.party_type as partyType,adp.parent_id as parentId,adp.tousu, ");
        sb.append("  BB.chaoqi,pt.empiric_value as jifen,ele.ele_count as eleCount,pro.pro_count as proCount ,user.user_count as userCount from ( ");

        sb.append("  SELECT np.id,np.party_name,np.party_type,np.parent_id,np.party_addr,sum(np.num) AS tousu FROM( ");
        sb.append("  SELECT p.*, ee.num FROM party p LEFT JOIN( ");
        sb.append(" SELECT count(ea.id)AS num, e.used_party_id AS upid, ");
        sb.append("  e.maintenance_party_id AS mpid FROM ");
        sb.append("  elevator_appraisal ea LEFT JOIN elevator e ON e.id = ea.elevator_id ");
        sb.append("  AND ea.evaluate_type = '2' GROUP BY e.used_party_id, e.maintenance_party_id ");
        sb.append("  )AS ee ON(p.id = ee.upid OR p.id = ee.mpid) ");
        sb.append("  GROUP BY p.id, ee.num )AS np ");
        sb.append("  GROUP BY np.id, np.party_name,np.party_type,np.parent_id,np.party_addr ");
        sb.append(" ) as adp left join (select elev.used_party_id, sum( ");
        sb.append("  (case when elev.next_check_date<(DATE_FORMAT(NOW(),'%Y%m%d')) ");
        sb.append(" THEN 1 else 0 end)) as chaoqi from elevator elev group by elev.used_party_id ");
        sb.append(" ) as BB on adp.id=BB.used_party_id ");

        sb.append(" left join promotion pt on (adp.id=pt.user_id and adp.party_type=pt.type) ");

        sb.append(" left join elevator endel on ");
        sb.append(" (adp.id=endel.used_party_id or adp.id=endel.maintenance_party_id) ");
        sb.append(" left join sub_party_info spi on adp.id = spi.id ");
        sb.append(" left join (select sum(case when place_type = '重点场所' then 1 else 0 end) dianti,used_party_id party_id from elevator group by used_party_id union ");
        sb.append(" select sum(case when place_type = '重点场所' then 1 else 0 end) dianti,maintenance_party_id party_id from elevator group by maintenance_party_id ) dt on adp.id = dt.party_id "
        		+ "left join (select ppp.id,count(1) ele_count from party ppp inner join elevator e on ppp.id = e.used_party_id or ppp.id = e.maintenance_party_id) ele on ele.id=adp.id "
        		+ "left join (select ppp.id,count(SUBSTRING_INDEX(SUBSTRING_INDEX(pc.contract_Name,',',help_topic_id+1),',',-1)) pro_count from party ppp inner join party_contract pc on ppp.id=pc.first_party or ppp.id=pc.second_party " 
				+ "INNER JOIN mysql.help_topic on 1=1 "
				+ "WHERE help_topic_id < LENGTH(pc.contract_Name)-LENGTH(REPLACE(pc.contract_Name,',',''))+1 "
				+ "group by ppp.id) pro on pro.id=adp.id "
        		+ "left join (select ppp.id,count(1) user_count from party ppp inner join party_user pu on ppp.id=pu.party_id inner join sys_user_role sur on pu.user_id=sur.user_id where sur.role_id in ('5','6','7')) user on user.id = adp.id ");

        sb.append("  where 1=1 ");
        if(map.get("tousu")!=null && !map.get("tousu").equals("")){
            sb.append("  and adp.tousu>"+map.get("tousu"));
        }

        if(map.get("parentId")!=null && !map.get("parentId").equals("")){
            sb.append("  and spi.parent_id="+map.get("parentId"));
        }
        if(map.get("tilin")!=null && !map.get("tilin").equals("")){
            sb.append("  and (DATE_FORMAT(NOW(),'%Y') - SUBSTRING(endel.elevator_num,11,4)) >"+map.get("tilin"));
        }
        if(map.get("jifen")!=null && !map.get("jifen").equals("")){
            sb.append("  and pt.empiric_value<"+map.get("jifen"));
        }
        if(map.get("chaoqi")!=null && !map.get("chaoqi").equals("")){
            sb.append("  and BB.chaoqi>"+map.get("chaoqi"));
        }
        if(map.get("dianti")!=null && !map.get("dianti").equals("")){
            sb.append("  and dt.dianti>"+map.get("dianti"));
        }
        if(map.get("partyType")!=null && !map.get("partyType").equals("")){
            sb.append("  and adp.party_type = "+map.get("partyType"));
        }else{
            sb.append("  and (adp.party_type='1' or adp.party_type='2') ");
        }

        sb.append("   group by adp.id,adp.party_name,adp.party_type,adp.tousu, ");
        sb.append("  BB.chaoqi,pt.empiric_value ");

        return sb.toString();
    }

}
